Creating Forecasts from Historical Data in Excel

Course: Advanced Excel Course

Topic: Creating Forecasts from Historical Data

Course Link: Advanced Excel Training

Demo Link: Register for a Course Demo

Website: JustAcademy

1. Introduction to Forecasting in Excel

Forecasting is the process of estimating future values using historical data, patterns, trends, and relationships. In Microsoft Excel, forecasting tools help users predict future sales, revenue, expenses, inventory demand, website traffic, customer orders, and other business measures.

For example, a retail company can analyze its monthly sales for the previous two years to estimate sales for the next three months. These estimates can help the company plan inventory, staffing, budgets, and marketing activities.

Excel provides several forecasting methods, including the Forecast Sheet feature, FORECAST.LINEAR, FORECAST.ETS, trendlines, and regression-related tools. The appropriate method depends on the type of data, the presence of trends or seasonal patterns, and the purpose of the forecast.

A forecast is an estimate rather than a guarantee. Actual results can differ because of market changes, unexpected events, pricing decisions, supply problems, and other factors that historical data may not capture.

2. Learning Objectives

• Understand the meaning and importance of forecasting.
• Prepare historical data for accurate forecasting in Excel.
• Identify trends, seasonality, and irregular fluctuations.
• Create a forecast using Excel's Forecast Sheet feature.
• Use FORECAST.LINEAR to estimate future values based on a linear trend.
• Use FORECAST.ETS for time-series data that may contain trends and seasonality.
• Create forecast charts and interpret confidence intervals.
• Evaluate forecast accuracy by comparing predicted and actual results.
• Apply forecasting techniques to sales, budgets, inventory, and business planning.

3. What Is Historical Data?

Historical data consists of observations recorded in the past. It may be organized by day, week, month, quarter, or year. Forecasting methods analyze these observations to estimate what may happen in future periods.

Example of Historical Sales Data

The data shows an upward pattern. A forecasting method can use this pattern to estimate sales for July and later months, provided the historical trend remains reasonably relevant.

4. Understanding the Main Components of Historical Data

Identifying these components helps determine which forecasting technique may be suitable. A steady trend may be modeled with linear forecasting, while recurring seasonal patterns may benefit from a seasonal time-series method.

5. Preparing Historical Data for Forecasting

Data quality is one of the most important factors in producing useful forecasts. Inconsistent dates, duplicate records, missing periods, and incorrect values can lead to misleading results.

Step 1: Organize the data

Keep the time period in one column and the corresponding numerical value in another column.

Month       | Sales
01-Jan-2026 | 80000
01-Feb-2026 | 85000
01-Mar-2026 | 90000
01-Apr-2026 | 95000
01-May-2026 | 100000
01-Jun-2026 | 105000

Step 2: Check the date format

• Ensure that dates are stored as actual Excel dates rather than text strings.
• Use consistent time intervals, such as the first day of every month.
• Sort dates chronologically when reviewing the dataset.
• Confirm that each observation corresponds to the correct period.

Step 3: Check the numerical values

• Make sure sales or other measurements are stored as numbers.
• Check for duplicate records and unexpected zeros.
• Investigate unusually high or low values before changing them.
• Identify missing periods and decide whether values can be recovered from reliable records.
• Aggregate detailed transactions to the required time period, such as monthly sales totals.

Step 4: Use consistent intervals

Time-series forecasting generally works best when observations are collected at consistent intervals. For example, monthly sales should have one observation per month. If raw data contains multiple transactions per day, summarize it into daily or monthly totals before forecasting when appropriate.

6. Choosing a Forecasting Method

No single method is best for every dataset. Compare methods using historical holdout data when possible, and select the approach that performs reasonably well for the intended use.

7. Creating a Forecast Using Excel's Forecast Sheet

Forecast Sheet is a convenient Excel feature for generating a forecast table and visual chart from time-based historical data. It uses an exponential smoothing approach and can provide forecast intervals when that option is enabled.

Step 1: Prepare the dataset

Enter historical dates in column A and the corresponding sales values in column B. Use regular monthly or other consistent intervals.

Step 2: Select the historical data

1. Select both the date column and the sales column, including the headings.
2. Confirm that the selected columns contain matching observations.
3. Make sure the dates and values represent the same periods.

Step 3: Open Forecast Sheet

1. Go to the Data tab.
2. Locate the Forecast group.
3. Select Forecast Sheet.
4. Choose a line chart or column chart for the forecast visualization.

Step 4: Set the forecast options

• Forecast End: Choose the final future date for the forecast.
• Forecast Start: Choose where the forecast begins if you want to compare earlier predictions with actual results.
• Confidence Interval: Choose whether to show the uncertainty range around forecast values.
• Seasonality: Let Excel detect a seasonal pattern automatically or set the period manually when justified.
• Timeline Range: Confirm the date range used for the forecast.
• Values Range: Confirm the corresponding historical values.
• Missing Points: Select an appropriate treatment for missing observations.
• Duplicate Dates: Choose how multiple observations with the same timestamp should be aggregated.

Step 5: Create the forecast

1. Review the settings and the forecast end date.
2. Click Create.
3. Excel generates a new worksheet with historical values, forecast values, and a chart.
4. Review the forecast table and the uncertainty interval before using the estimates in a report.

The Forecast Sheet interface and availability may vary by Excel version and platform. In versions without this feature, FORECAST.LINEAR or supported FORECAST.ETS formulas can be used instead.

8. Using the FORECAST.LINEAR Function

The FORECAST.LINEAR function estimates a future value using linear regression. It uses known x-values and y-values to fit a straight-line relationship and predicts the y-value for a new x-value.

Syntax

=FORECAST.LINEAR(x, known_y's, known_x's)

• x: The future point for which a value is required.
• known_y's: The historical values being predicted, such as sales.
• known_x's: The corresponding time values or other numeric independent values.

Example

Suppose month numbers are in A2:A7 and historical sales are in B2:B7.

To forecast sales for month 7, enter:

=FORECAST.LINEAR(7,B2:B7,A2:A7)

The forecast is ₹110,000 because the sample values follow a straight-line increase of ₹5,000 per month.

This result is a simple illustration. Real business data usually contains variation, and a linear trend should not be extended indefinitely without checking whether it remains reasonable.

9. Using the FORECAST.ETS Function

FORECAST.ETS estimates future values using exponential smoothing. It can account for level, trend, and seasonal patterns when they are supported by the data.

Syntax

=FORECAST.ETS(target_date, values, timeline, [seasonality], [data_completion], [aggregation])

• target_date: The future date or time point to predict.
• values: The historical numerical observations.
• timeline: The dates or numeric time points corresponding to the observations.
• seasonality: Optional setting that lets Excel detect seasonality automatically or use a specified seasonal length.
• data_completion: Optional setting controlling how missing timeline points are handled.
• aggregation: Optional method for combining duplicate timestamps, such as average or sum.

Example

Suppose monthly dates are in A2:A25 and monthly sales values are in B2:B25. Cell A26 contains the next month’s date.

=FORECAST.ETS(A26,B2:B25,A2:A25)

The formula estimates sales for the date in A26 using the historical time series. The actual result depends on the historical values and the model fitted by Excel.

Important: The timeline should have a consistent interval, and duplicate timestamps can cause errors unless the data is aggregated or handled appropriately. FORECAST.ETS is not available in Excel for the web, iOS, or Android according to Microsoft's function documentation; availability depends on the Excel version.

10. Understanding Linear Forecasting and Seasonal Forecasting

For example, a business with steady monthly growth and little seasonal variation might begin with linear forecasting. A retailer whose sales rise repeatedly during particular months may benefit from testing ETS forecasting.

11. Creating a Forecast Chart with Historical Data

A forecast chart combines historical observations with projected values so that users can compare the past pattern with the expected future direction.

Step 1: Arrange the data

Create columns for Date, Actual Sales, and Forecast Sales.

Date       | Actual Sales | Forecast Sales
Jan        | 80000        |
Feb        | 85000        |
Mar        | 90000        |
Apr        | 95000        |
May        | 100000       |
Jun        | 105000       |
Jul        |              | 110000

Step 2: Insert a chart

1. Select the date and the actual and forecast series.
2. Open the Insert tab.
3. Choose a Line Chart for a time-based comparison.
4. Confirm that the dates appear in chronological order.

Step 3: Format the chart

• Use a clear title such as Historical Sales and Forecast.
• Label the horizontal axis with time periods.
• Label the vertical axis with the measure and its units.
• Make the historical and forecast series visually distinguishable.
• Include a legend if more than one series appears.
• Use consistent number formats for all values.

When adding forecast values manually, keep the historical and forecast periods clearly distinguishable. Avoid presenting estimated values as actual results.

12. Understanding Confidence Intervals

A confidence interval in a forecast worksheet represents an estimated range around a forecast value. It communicates uncertainty rather than providing a guaranteed minimum and maximum.

For example, a forecast might estimate monthly sales at ₹110,000 with an interval from ₹102,000 to ₹118,000. This indicates that the estimate has uncertainty under the assumptions of the forecasting method.

• A narrower interval generally indicates less forecast uncertainty for that point under the fitted model.
• A wider interval indicates greater uncertainty.
• Intervals often widen as the forecast horizon increases.
• Unusual events or structural changes may cause actual results to fall outside the interval.

Do not treat a confidence interval as a promise that the future value must fall within its limits. It is a model-based estimate whose usefulness depends on the quality of the data and assumptions.

13. Evaluating Forecast Accuracy

A forecast should be evaluated against actual outcomes whenever possible. A practical approach is to reserve some historical periods for testing rather than using all observations to build the forecast.

13.1 Forecast Error

Forecast error is the difference between the actual value and the predicted value.

Formula: Forecast Error = Actual Value − Forecast Value

=B2-C2

If actual sales are in B2 and forecast sales are in C2, the formula calculates the forecast error for that period.

13.2 Absolute Error

Absolute error measures the size of the error without considering its direction.

=ABS(B2-C2)

13.3 Mean Absolute Error (MAE)

MAE is the average of the absolute forecast errors. A smaller MAE generally indicates more accurate forecasts for the same dataset and measurement scale.

=AVERAGE(D2:D13)

Here, D2:D13 contains the absolute errors for the tested periods.

13.4 Mean Absolute Percentage Error (MAPE)

MAPE calculates the average absolute error as a percentage of the actual values. It can be useful when actual values are positive and nonzero.

=AVERAGE(E2:E13)

For this formula, E2:E13 should contain percentage errors calculated for each period, for example:

=IF(B2=0,NA(),ABS((B2-C2)/B2))

Format the results as percentages. MAPE is not appropriate when actual values are zero, and it can be misleading when actual values are very close to zero.

13.5 Root Mean Squared Error (RMSE)

RMSE calculates the square root of the average squared forecast error. It penalizes larger errors more heavily than MAE.

=SQRT(SUMSQ(B2:B13-C2:C13)/COUNT(B2:B13))

In Excel versions that do not evaluate the range calculation in this formula as intended, create a separate error column, square each error in another column, and calculate the square root of the average squared errors.

Forecast metrics should be interpreted alongside the scale and business impact of the measure. A smaller error does not automatically mean that a forecasting model is suitable for every future period.

14. Practical Example: Forecasting Monthly Sales

Assume a company has recorded sales for six months and wants to estimate sales for July.

Because sales increase by ₹5,000 each month in this simplified dataset, the linear forecast for July is ₹110,000.

Business interpretation: If the upward trend continues, July sales may reach ₹110,000. However, the business should check for seasonal changes, price changes, marketing campaigns, supply constraints, and other factors before using the estimate for budgeting or inventory planning.

15. Forecasting for Different Business Scenarios

Sales Forecasting

Use historical sales to estimate future revenue, support sales targets, and plan staffing. Where relevant, analyze products and regions separately rather than relying only on company-wide totals.

Inventory Forecasting

Use historical demand to estimate future stock requirements. Consider delivery lead times, stockouts, promotions, and seasonal peaks because past sales may not fully reflect unmet demand.

Budget Forecasting

Forecast expenses, cash inflows, and operational costs to support financial planning. Review major business changes that could make historical relationships unreliable.

Website Traffic Forecasting

Analyze daily or weekly traffic to estimate future visits and plan campaigns or server capacity. Consider campaigns, search changes, and unusual traffic spikes.

Customer Demand Forecasting

Use order volumes and customer activity to estimate likely demand. Where possible, compare forecasts with actual outcomes and update the model as new observations become available.

16. Common Forecasting Errors and Troubleshooting

17. Best Practices for Forecasting in Excel

• Use reliable historical data with consistent time intervals.
• Aggregate raw transactions to the required time period before forecasting.
• Check missing periods, duplicates, outliers, and data-entry errors.
• Choose a method appropriate to the trend and seasonal behavior.
• Use sufficient historical data to represent recurring patterns where possible.
• Compare more than one reasonable method when the decision is important.
• Test forecasts against periods that were not used to fit the model.
• Track actual values and forecast errors over time.
• Review forecasts whenever business conditions change.
• Communicate assumptions, limitations, and uncertainty in reports.
• Avoid extending a linear trend too far beyond the observed data without validation.
• Do not treat forecasts as guaranteed outcomes or substitute them for business judgment.

18. Practice Exercise

Enter the following monthly sales data in Excel:

Complete these tasks:

1. Enter month numbers from 1 to 6 in a separate column.
2. Use FORECAST.LINEAR to estimate July and August sales.
3. Create a Forecast Sheet if the feature is available in your Excel version.
4. Compare the forecast chart with the historical sales trend.
5. Explain whether the forecast seems reasonable and identify assumptions that could affect actual sales.
6. If actual July sales later become available, compare them with the forecast and calculate the absolute error.

Practice Formulas

Linear Forecast for July:
=FORECAST.LINEAR(7,B2:B7,A2:A7)

Linear Forecast for August:
=FORECAST.LINEAR(8,B2:B7,A2:A7)

Forecast Error:
=Actual_Value-Forecast_Value

Absolute Error:
=ABS(Actual_Value-Forecast_Value)

Assume the month numbers are in A2:A7 and sales are in B2:B7. Adjust the cell references to match your worksheet.

19. Mini Project: Monthly Revenue Forecast

Objective: Create a forecast report that estimates future revenue using historical monthly sales data.

Project Requirements

1. Collect at least 12 months of historical sales data, preferably more if seasonal patterns are important.
2. Clean the dataset and confirm that each date matches the corresponding revenue value.
3. Create a line chart to inspect historical trends and potential seasonality.
4. Use FORECAST.LINEAR to generate a baseline forecast.
5. Use FORECAST.ETS or Forecast Sheet where available and appropriate.
6. Forecast the next three to six months.
7. Display historical sales and projected values in a table and chart.
8. Compare forecast methods against actual historical values where possible.
9. Calculate forecast errors using suitable metrics such as MAE or RMSE.
10. Write a short report explaining the results, assumptions, and limitations.

Expected Deliverables

• A cleaned historical dataset.
• A forecast table with dates and projected revenue.
• A chart showing actual historical values and forecast values.
• A short accuracy assessment.
• A business summary describing how the forecast could support planning.

20. Interview Questions and Answers

Q1. What is forecasting in Excel?

Forecasting uses historical observations and a suitable model to estimate future values. Excel provides functions and tools for generating these estimates.

Q2. What is the difference between historical data and forecast data?

Historical data contains observed values from the past, while forecast data contains estimates for future periods.

Q3. What is FORECAST.LINEAR?

FORECAST.LINEAR predicts a value using a linear regression relationship between known x-values and known y-values.

Q4. What is FORECAST.ETS?

FORECAST.ETS predicts future values using exponential smoothing and can account for trends and seasonal patterns supported by the time series.

Q5. What is seasonality in forecasting?

Seasonality is a pattern that repeats at a regular interval, such as higher retail sales during a recurring holiday season.

Q6. What is a confidence interval in a forecast?

It is a model-based range that communicates uncertainty around the estimated forecast value.

Q7. Why is historical data cleaning important?

Incorrect dates, missing periods, duplicate records, and invalid numbers can distort trends and lead to unreliable forecasts.

Q8. How can forecast accuracy be measured?

Compare forecasts with actual values and calculate measures such as MAE, RMSE, or MAPE when its assumptions are appropriate.

Q9. Why can a forecast be inaccurate even when the formulas are correct?

The underlying model may not capture changing market conditions, unusual events, seasonality, or structural changes in the data.

Q10. What is the purpose of a Forecast Sheet?

Forecast Sheet generates a table of historical and forecast values along with a chart, making it easier to visualize future estimates.

21. Quick Revision Checklist

• Historical data: past observations used to estimate future values.
• Trend: long-term upward or downward direction.
• Seasonality: a repeating pattern at regular intervals.
• Forecast Sheet: Data > Forecast Sheet.
• Linear forecasting: =FORECAST.LINEAR(x,known_y's,known_x's)
• ETS forecasting: =FORECAST.ETS(target_date,values,timeline)
• Forecast error: Actual value minus forecast value.
• Absolute error: =ABS(Actual-Forecast)
• MAE: average absolute error.
• RMSE: square root of average squared forecast errors.
• MAPE: average absolute percentage error, not suitable when actual values are zero.
• Always check data quality and evaluate forecast accuracy before using results for business decisions.

22. Conclusion

Creating forecasts from historical data in Excel helps businesses estimate future demand, sales, revenue, expenses, and operational requirements. By preparing data correctly, identifying trends and seasonal patterns, and selecting suitable forecasting methods, users can create meaningful projections for planning and reporting.

Excel's Forecast Sheet, FORECAST.LINEAR, and FORECAST.ETS provide different ways to generate estimates. Forecast accuracy should be checked against actual outcomes, and uncertainty should be communicated clearly. A forecast is most useful when it is reviewed regularly and combined with sound business judgment.

23. Related Course Resources

Explore the Advanced Excel Course to develop your spreadsheet, reporting, and data analysis skills.

Interested in learning more? Register for a course demo to explore the training options.

Visit the JustAcademy website for additional learning resources.

Reference

For additional details on Excel forecasting functions, refer to Microsoft Support: FORECAST and FORECAST.LINEAR, Microsoft Support: FORECAST.ETS, and Microsoft Support: Create a Forecast in Excel.